﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;

namespace DAL
{
    /// <summary>
    /// DAL to Lease table
    /// </summary>
    public abstract class LeaseDAL : BaseDAL, UTL.IBaseDAL
    {
        #region Contansts
        #endregion

        #region Objects
        #endregion

        #region Properties
        #endregion

        #region Constructors
        public LeaseDAL() { }
        public LeaseDAL(string connectString) : base(connectString) { }
        #endregion

        #region Implements
        /// <summary>
        /// Count all of records
        /// </summary>
        /// <returns>Number of records</returns>
        public int Count()
        {
            return _mdb.tb_Leases.Count();
        }

        /// <summary>
        /// Select all data from table in database
        /// </summary>
        /// <returns>Data</returns>
        public DataTable Select()
        {
            try
            {
                var res = from s in _mdb.tb_Leases
                          orderby s.Id descending
                          select new
                          {
                              s.Id,
                              s.GuestId,
                              GuestName = s.tb_Guest.Name,
                              s.JoinerId,
                              JoinerName = s.tb_Guest1.Name,
                              s.RoomId,
                              RoomName = s.tb_Room.Name,
                              s.LeaseTypeId,
                              LeaseTypeName = s.tb_LeaseType.Name,
                              s.UserId,
                              UserName = s.tb_User.Name,
                              s.ComingDate,
                              s.LeaveDate,
                              s.OldElec,
                              s.NewElec,
                              s.OldWater,
                              s.NewWater,
                              s.PriceElec,
                              s.PriceWater
                          };
                return res.ToDataTable();
            }
            catch { return _dtb; }
        }

        /// <summary>
        /// Select data by object from table in database
        /// </summary>
        /// <param name="obj">Condition</param>
        /// <returns>Data</returns>
        public DataTable Select(object obj)
        {
            var id = (long)obj;

            try
            {
                var res = from s in _mdb.tb_Leases
                          where s.RoomId == id
                          orderby s.ComingDate
                          select new
                          {
                              s.Id,
                              s.GuestId,
                              GuestName = s.tb_Guest.Name,
                              s.JoinerId,
                              JoinerName = s.tb_Guest1.Name,
                              s.RoomId,
                              RoomName = s.tb_Room.Name,
                              s.LeaseTypeId,
                              LeaseTypeName = s.tb_LeaseType.Name,
                              s.UserId,
                              UserName = s.tb_User.Name,
                              s.ComingDate,
                              s.LeaveDate,
                              s.OldElec,
                              s.NewElec,
                              s.OldWater,
                              s.NewWater,
                              s.PriceElec,
                              s.PriceWater
                          };
                return res.ToDataTable();
            }
            catch { return _dtb; }
        }

        /// <summary>
        /// Get a object by key
        /// </summary>
        /// <param name="key">Key need to get</param>
        /// <returns>Object</returns>
        public object GetByKey(object key)
        {
            var id = (long)key;
            try
            {
                var res = from s in _mdb.tb_Leases
                          where s.RoomId == id
                          orderby s.Id descending
                          select s;
                return res.Take(1).Single();
            }
            catch { return null; }
        }

        /// <summary>
        /// Delete data by id
        /// </summary>
        /// <param name="id">Id</param>
        /// <returns>True is successfull else false</returns>
        public UTL.Session.PerState Delete(long id)
        {
            try
            {
                _mdb = new xG14DataContext();

                var res = from s in _mdb.tb_Leases
                          where s.Id == id
                          select s;

                if (res != null)
                {
                    var et = res.Single();
                    _mdb.tb_Leases.DeleteOnSubmit(et);

                    var x = _mdb.tb_Rooms.Single(k => k.Id == et.RoomId);
                    x.StatusId = 1; // empty

                    _mdb.SubmitChanges();
                    return UTL.Session.PerState.Success;
                }
                else { return UTL.Session.PerState.Undelete; }
            }
            catch { return UTL.Session.PerState.Undelete; }
        }

        /// <summary>
        /// Insert object to table in database
        /// </summary>
        /// <param name="obj">Object need to insert</param>
        /// <returns>True is successfull else false</returns>
        public UTL.Session.PerState Insert(object obj)
        {
            var o = (tb_Lease)obj;

            try
            {
                _mdb.tb_Leases.InsertOnSubmit(o);

                // Update room's status
                var roo = _mdb.tb_Rooms.Single(k => k.Id == o.RoomId);
                roo.StatusId = 2; // used

                _mdb.SubmitChanges();
                return UTL.Session.PerState.Success;
            }
            catch { return UTL.Session.PerState.Uninsert; }

        }

        /// <summary>
        /// Update object to table in database
        /// </summary>
        /// <param name="obj">Object need to update</param>
        /// <returns>True is successfull else false</returns>
        public UTL.Session.PerState Update(object obj)
        {
            var o = (tb_Lease)obj;

            try
            {
                var res = _mdb.tb_Leases.Single(k => k.Id == o.Id);

                res.GuestId = o.GuestId;
                res.JoinerId = o.JoinerId;
                res.RoomId = o.RoomId;
                res.LeaseTypeId = o.LeaseTypeId;
                res.UserId = o.UserId;
                res.ComingDate = o.ComingDate;
                res.LeaveDate = o.LeaveDate;
                res.OldElec = o.OldElec;
                res.NewElec = o.NewElec;
                res.OldWater = o.OldWater;
                res.NewWater = o.NewWater;
                res.PriceElec = o.PriceElec;
                res.PriceWater = o.PriceWater;

                // Update room's status
                var roo = _mdb.tb_Rooms.Single(k => k.Id == o.RoomId);
                roo.StatusId = 2; // used

                _mdb.SubmitChanges();
                return UTL.Session.PerState.Success;
            }
            catch { return UTL.Session.PerState.Unupdate; }
        }
        #endregion

        #region Events
        #endregion

        #region Methods
        #endregion

        #region Overrides
        #endregion

        #region Statics
        #endregion

        #region More
        #endregion
    }
}